﻿using System;
using System.Collections.Generic;
using System.Text;
using KPIS.GERP.GIMS.MODEL;
using KPIS.DBM;
using System.Configuration;
using System.Data;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_Requisition_Desc
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_Requisition_Desc> GetListFillCode(string requisition_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_Requisition_Desc> infoList = new List<M_Requisition_Desc>();
            try
            {
                dbManager.Open();

                strSQL = " SELECT requisiton_des_item,rqdesc.requisition_code,requisition_item "
                         + " ,quantity,rqdesc.icm_res_item_seq "
                         + " ,concat(coalesce(icm_res_item_code, ''), '  ', coalesce(icm_res_item_name, '')) as icm_res_item_name "
                         + " ,job_site_seq "
                         + " FROM icm_requisition_desc rqdesc "
                         + " LEFT JOIN icm_requisition rq ON rqdesc.requisition_code = rq.requisition_code "
                         + " LEFT JOIN icm_mas_resource_item rt ON rqdesc.icm_res_item_seq = rt.icm_res_item_seq "
                         + " WHERE rqdesc.record_status <>'D' and rqdesc.requisition_code LIKE '%' '" + requisition_code + "'  '%' ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_Requisition_Desc info = new M_Requisition_Desc();
                        info.requisiton_des_item = Convert.ToInt32(drc[i]["requisiton_des_item"]);
                        info.requisition_code = drc[i]["requisition_code"].ToString();
                        info.requisition_item = Convert.ToInt32(drc[i]["requisition_item"]);
                        info.quantity = Convert.ToDouble(drc[i]["quantity"].ToString());
                        info.icm_res_item_seq = Convert.ToInt32(drc[i]["icm_res_item_seq"]);
                        info.icm_res_item_name = drc[i]["icm_res_item_name"].ToString();
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.icm_res_item_seq = Convert.ToInt32(drc[i]["icm_res_item_seq"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }


        public M_Requisition_Desc GetListFillBySeq(Nullable<Int32> requisiton_des_item)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_Requisition_Desc info = new M_Requisition_Desc();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@requisiton_des_item", requisiton_des_item);

                strSQL = strSQL = " SELECT requisiton_des_item,rqdesc.requisition_code,requisition_item "
                         + " ,quantity,rqdesc.icm_res_item_seq "
                         + " ,concat(coalesce(icm_res_item_code, ''), '  ', coalesce(icm_res_item_name, '')) as icm_res_item_name "
                         + " FROM icm_requisition_desc rqdesc "
                         + " LEFT JOIN icm_requisition rq ON rqdesc.requisition_code = rq.requisition_code "
                         + " LEFT JOIN icm_mas_resource_item rt ON rqdesc.icm_res_item_seq = rt.icm_res_item_seq "
                         + " WHERE rqdesc.record_status <>'D' and requisiton_des_item= @requisiton_des_item ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.requisiton_des_item = Convert.ToInt32(drc[0]["requisiton_des_item"]);
                    info.requisition_code = drc[0]["requisition_code"].ToString();
                    info.requisition_item = Convert.ToInt32(drc[0]["requisition_item"]);
                    info.quantity = Convert.ToDouble(drc[0]["quantity"].ToString());
                    info.icm_res_item_seq = Convert.ToInt32(drc[0]["icm_res_item_seq"]);
                    info.icm_res_item_name = drc[0]["icm_res_item_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public Int32 GetRequestItem(string requisition_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            //M_Requisition_Desc info = new M_Requisition_Desc();
            int requestitem = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@requisition_code", requisition_code);

                strSQL = " SELECT count(rqdesc.requisition_code)+1 as num_requisition_item "
                        +" FROM icm_requisition_desc rqdesc "
                        +" LEFT JOIN icm_requisition rq ON rqdesc.requisition_code = rq.requisition_code "
                        + " WHERE rq.record_status <>'D' and rqdesc.requisition_code = '@requisition_code' ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    requestitem = Convert.ToInt32(drc[0]["num_requisition_item"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return requestitem;
        }
        public void Insert_Requisition_Desc(M_Requisition_Desc info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@requisition_code", info.requisition_code);
                dbManager.AddParameters(1, "@requisition_item", info.requisition_item);
                dbManager.AddParameters(2, "@quantity", info.quantity);
                dbManager.AddParameters(3, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(4, "@record_status", info.record_status);

                strSQL = " INSERT INTO icm_requisition_desc "
                        + " (requisition_code,requisition_item,quantity "
                        + " ,icm_res_item_seq,record_status) "
                        + " VALUES "
                        + " (@requisition_code,@requisition_item,@quantity "
                        + " ,@icm_res_item_seq,@record_status) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_RequisitionDesc(M_Requisition_Desc info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@requisiton_des_item", info.requisiton_des_item);
                dbManager.AddParameters(1, "@quantity", info.quantity);
                dbManager.AddParameters(2, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(3, "@record_status", info.record_status);

                strSQL = " UPDATE icm_requisition_desc "
                        + " SET quantity = @quantity "
                        + " ,icm_res_item_seq =@icm_res_item_seq "
                        + " ,record_status = @record_status "
                        + " WHERE requisiton_des_item = @requisiton_des_item ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
